********************************************************************************
* MANAGE IOT RAW DATA & IMPUTE CONSUMPTION 
* File description: Cleaning raw delivery from SCB and impute household consumption
********************************************************************************
                           	
local cpi2007 1.08922240198272
local cpi2006 1.11332770389135
local cpi2005 1.12849500713267
local cpi2004 1.1333452722063
local cpi2003 1.13782811938152
local cpi2002 1.15993401759531
local cpi2001 1.18468738300262
local cpi2000 1.21377062
local cpi1999 1.22599768


forv j=1999/2000 {
use LopNr cdisp kiranta istud uater fkubank cdisph akupens kv kf kakurta skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab fkuvp using "raw\IoT\iot`j'.dta", clear

duplicates drop
duplicates drop LopNr, force

gen year=`j'

foreach var in cdisp kiranta istud uater fkubank akupens kv kf kakurta skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab fkuvp cdisph {
	
replace `var'=`var'*`cpi`j''

}

gen ismlan=0
replace ismlan=istud-17854 if istud>17854 /*Remove studiebidrag from istud to get only the loan part*/

tempfile iot`j'
save `iot`j''

}

forv j=2001/2003 {
use LopNr cdisp kiranta ismlan fkubank uater kv kf akupens kakurta skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab fkuvp cdisph using "raw\IoT\iot`j'.dta", clear

duplicates drop
duplicates drop LopNr, force

gen year=`j'

foreach var in cdisp kiranta ismlan fkubank uater kv kf akupens kakurta skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab fkuvp cdisph {
	
replace `var'=`var'*`cpi`j''

}


tempfile iot`j'
save `iot`j''
}


local j=2004 

use LopNr cdisp kiranta ismlan uater fkubank kv kf kakurta akupens skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab fkuvp cdisph using "raw\IoT\iot`j'.dta", clear

duplicates drop
duplicates drop LopNr, force

gen year=`j'

foreach var in cdisp kiranta ismlan uater fkubank kv kakurta kf akupens skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab cdisph fkuvp {
	
replace `var'=`var'*`cpi`j''

}


tempfile iot`j'
save `iot`j''

forv j=2005/2007 {
use LopNr cdisp kiranta ismlan uater fkubank kvbrut kfbrut kakurta akupens skubank skuvp kkuvp nakte nakthb npase npashb tfoab fkuvp cdisph using "raw\IoT\iot`j'.dta", clear

duplicates drop
duplicates drop LopNr, force

gen year=`j'

foreach var in cdisp kiranta ismlan uater fkubank kvbrut kakurta kfbrut akupens skubank skuvp kkuvp nakte nakthb npase npashb tfoab cdisph fkuvp {
	
replace `var'=`var'*`cpi`j''

}


tempfile iot`j'
save `iot`j''
}


forval j= 1999(1)2006 {
append using `iot`j''
}

tempfile iot
save `iot'

********************************************************************************
*Derivatives ownership
forv j=2000/2007 {
use lopnr using "\rawKURU\kuru`j'_ku33.dta" , clear

rename lopnr LopNr
duplicates drop

gen derivatives=1

gen year=`j'	

tempfile kuru`j'
save `kuru`j''

}


forval j= 2000(1)2006 {
append using `kuru`j''
}

tempfile kuru
save `kuru'


********************************************************************************
*Wealth and debt variables from FORM, merge with IoT

forv j=1999/2007 {
use lopnr fskulmv fnettmv ffors using "raw\FORM\form`j'.dta" , clear

collapse (sum) fskulmv (sum) ffors (sum) fnettmv, by(lopnr)

gen year=`j'

rename lopnr LopNr

replace fskulmv=fskulmv*`cpi`j''
replace ffors=ffors*`cpi`j''
replace fnettmv=fnettmv*`cpi`j''

tempfile form`j'
save `form`j''

}

forval j= 1999(1)2006 {
append using `form`j''
}

tempfile form
save `form'

tab _merge
keep if _merge==3
drop _merge

merge 1:1 LopNr year using `iot'
tab _merge
drop if _merge==2
drop _merge


merge 1:1 LopNr year using `form'
tab _merge
drop if _merge==2
drop _merge

merge 1:1 LopNr year using `kuru'
tab _merge
drop if _merge==2
drop _merge

mvencode fskulmv skubank skuvp cdisp cdisph kiranta ismlan uater fkubank kvbrut kfbrut akupens kv kf ffors skubank skuvp kkuvp nakte nakthb npase npashb nutland tfoab kakurta fkuvp istud  derivatives,mv(0)o

sort LopNr year
tsset LopNr year

*Impute consumption following Koijen et al 2011

*Define disposable income

g dispinc=0
replace dispinc=cdisp-kiranta-ismlan+uater-max((kv-kf),0) if year>=1999 & year<=2004
replace dispinc=cdisp-kiranta-ismlan+uater-kvbrut+kfbrut if year>=2005 & year<=2007

*Net debt
*Change in total debt 
 g delta_debt=fskulmv-L.fskulmv
 
*Adjust for those who enter data after 1998

bys LopNr: gen x=_n
gen y=0
replace y=1 if x==1 & year!=1999
bys LopNr: egen yy=max(y)

replace delta_debt=fskulmv if x==1 & yy==1

label var yy "Enter data after 1999"  
drop y

*Change in net wealth, to be used in sample restrictions section below

g delta_wealth=fnettmv-L.fnettmv

replace delta_wealth=0 if x==1 & yy==1

*Debt service
g debt_service=kakurta


*Bank account
*Changes in bank account

g bank=fkubank-L.fkubank

replace bank=fkubank if x==1 & yy==1


*Income from financial assets
g y_v=kiranta-skubank-kkuvp-skuvp


*Capital insurance accounts

*From fondbolagen.se six-index
local sixrx1999 0.6976
local sixrx2000 -0.108
local sixrx2001 -0.1484
local sixrx2002 -0.359
local sixrx2003 0.3416
local sixrx2004 0.2075
local sixrx2005 0.3632
local sixrx2006 0.2806
local sixrx2007 -0.026


g capital_insurance=0

forval j=2000(1)2007 {
replace capital_insurance=ffors-L.ffors*(1+`sixrx`j'') if year==`j'

}

replace capital_insurance=ffors if x==1 & yy==1


*Pension savings

g pension=akupens


foreach var in capital_insurance dispinc delta_debt delta_wealth debt_service bank pension {
	
replace `var'=0 if mi(`var')

}

*Impute consumption

gen consumption=dispinc+delta_debt-debt_service-bank+y_v-capital_insurance-pension

drop if year<2000

*Create household variables
bys LopNr_family year: egen consumptionh=sum(consumption)


gen businessinc=nakte+nakthb+npase+npashb+nutland+tfoab

bys LopNr_family year: egen businessinch=sum(businessinc)
bys LopNr_family year: egen fkuvph=sum(fkuvp)
bys LopNr_family year: egen movedh=sum(moved)
bys LopNr_family year: egen ownershifth=sum(ownershift)
bys LopNr_family year: egen kirantah=sum(kiranta)
bys LopNr_family year: egen delta_wealthh=sum(delta_wealth)
bys LopNr_family year: egen derivativesh=sum(derivatives)


replace consumption=0 if mi(consumption)


label var consumptionh "Household consumption"

sum consumptionh, d

rename LopNr id 
rename LopNr_family idfam

gen z=id if head==1

replace z=0 if mi(z)

bys idfam year: egen id_head=max(z)



drop z


duplicates drop 

*Stocks, bonds and mutual funds

replace consumptionh=consumptionh-pchange

sum consumptionh 


*Sample restriction
*Drop households with unstable head or unstable number of adults

*1. Drop households that change number of adults 

bys idfam: egen max_adults=max(adult)
bys idfam: egen min_adults=min(adult)

drop if adult!=max_adult
drop if adult!=min_adult
drop min_adults max_adults

**2. Drop households that change head
sort idfam year
gen yy=0
bys idfam: replace yy=1 if id_head!=id_head[_n-1]

bys idfam: gen xx=_n

replace yy=0 if xx==1

*Gen indicator for familes who change head
bys idfam: egen yyy=max(yy)

drop if yyy==1

sum consumptionh, d
drop yyy yy xx 

*3. drop farmers and entrepreneurs

drop if businessinch>50000

sum consumptionh, d

*4. households with inconsistent homeownership
drop if ownershifth>0 /*Moved from house, according to FAS-registry */

drop if movedh>0 /*Moved from coop, BRF and BTR registry*/

sum consumptionh, d

*5. households who hold derivatives
*Dropping all households in KU33
drop if derivativesh>0 

*6. extreme portfolio returns (top and bottom 1%)

bys year: egen p1_returns=pctile(kirantah), p(1)
bys year: egen p99_returns=pctile(kirantah), p(99)

keep if inrange(kirantah,p1_returns,p99_returns)

*7. households with big changes in net worth (top 2.5% changes)

_pctile delta_wealthh, p(97.5)
local wealth=r(r1)
keep if delta_wealthh<`wealth'

keep LopNr_family consumptionh

save "use\household_consumption.dta", replace
